package Lianxi1022;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import work1018.DBUtil;

public class StudentDao {

	public List<Student> findALL() {
		String sql = "select * from s_student";
		List<Student> list = new ArrayList<>();
		Connection con = DBUtil.getConnection();
		PreparedStatement st = null;
		ResultSet rs = null;
		try {
			st = con.prepareStatement(sql);
			rs = st.executeQuery();
			while (rs.next()) {
				Student s = new Student();
				s.setStuno(rs.getInt("stuno"));
				s.setSname(rs.getString("sname"));
				s.setSex(rs.getString("sex"));
				s.setTelephone(rs.getString("telephone"));
				s.setQq(rs.getString("qq"));
				s.setWechat(rs.getString("wechat"));
				s.setGradeid(rs.getInt("gradeid"));
				s.setEnterdate(rs.getString("enterdate"));
				s.setFromcity(rs.getString("fromcity"));
				s.setBirthday(rs.getString("birthday"));
				s.setAddress(rs.getString("address"));
				s.setPass(rs.getString("pass"));
				s.setCreatetime(rs.getString("createtime"));
				list.add(s);
			}
		} catch (SQLException e) {
			// TODO 閼奉亜濮╅悽鐔稿灇閻拷 catch 閸э拷
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, st, con);
		}
		return list;
	}

	// add
	public int add(Student s) {
		String sql = "insert into s_student( stuno,sname,sex,telephone,qq,wechat,gradeid,enterdate,fromcity,birthday,address,pass,createtime)values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
		Connection con = DBUtil.getConnection();
		PreparedStatement st = null;
		try {
			st = con.prepareStatement(sql);
			st.setLong(1, s.getStuno());
			st.setString(2, s.getSname());
			st.setString(3, s.getSex());
			st.setString(4, s.getTelephone());
			st.setString(5, s.getQq());
			st.setString(6, s.getWechat());
			st.setLong(7, s.getGradeid());
			st.setString(8, s.getEnterdate());
			st.setString(9, s.getFromcity());
			st.setString(10, s.getBirthday());
			st.setString(11, s.getAddress());
			st.setString(12, s.getPass());
			st.setString(13, s.getCreatetime());
			return st.executeUpdate();
		} catch (SQLException e) {
			// TODO 閼奉亜濮╅悽鐔稿灇閻拷 catch 閸э拷
			e.printStackTrace();
		} finally {
			DBUtil.close(null, st, con);
		}
		return 0;
	}

	public int update(Student s) {
		return 0;
	}

	public int del(long id) {
		return 0;
	}

	// 閺嶈宓佹稉濠氭暛閺屻儲澹樻稉锟芥稉锟�

	public Student get(long stuno) {

		String sql = "select * from s_student where stuno=?";

		Connection con = DBUtil.getConnection();
		PreparedStatement st = null;
		ResultSet rs = null;
		try {
			st = con.prepareStatement(sql);
			st.setLong(1, stuno);
			rs = st.executeQuery();
			if (rs.next()) {
				Student s = new Student();
				s.setStuno(rs.getInt("stuno"));
				s.setSname(rs.getString("sname"));
				s.setSex(rs.getString("sex"));
				s.setTelephone(rs.getString("telephone"));
				s.setQq(rs.getString("qq"));
				s.setWechat(rs.getString("wechat"));
				s.setGradeid(rs.getInt("gradeid"));
				s.setEnterdate(rs.getString("enterdate"));
				s.setFromcity(rs.getString("fromcity"));
				s.setBirthday(rs.getString("birthday"));
				s.setAddress(rs.getString("address"));
				s.setPass(rs.getString("pass"));
				s.setCreatetime(rs.getString("createtime"));
				return s;
			}
		} catch (SQLException e) {
			// TODO 閼奉亜濮╅悽鐔稿灇閻拷 catch 閸э拷
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, st, con);
		}
		return null;
	}

	/**
	 * pageNo妞ゅ灚鏆� pageSize:妞ら潧銇囩亸锟�
	 */
	public List<Student> paging(int pageNo, int pageSize) {

		String sql = "select * from s_student order by stuno asc limit ?,?";
		List<Student> list = new ArrayList<>();
		Connection con = DBUtil.getConnection();
		PreparedStatement st = null;
		ResultSet rs = null;
		try {
			st = con.prepareStatement(sql);
			st.setInt(1, (pageNo - 1) * pageSize);
			st.setInt(2, pageSize);
			rs = st.executeQuery();
			while (rs.next()) {
				Student s = new Student();
				s.setStuno(rs.getInt("stuno"));
				s.setSname(rs.getString("sname"));
				s.setSex(rs.getString("sex"));
				s.setTelephone(rs.getString("telephone"));
				s.setQq(rs.getString("qq"));
				s.setWechat(rs.getString("wechat"));
				s.setGradeid(rs.getInt("gradeid"));
				s.setEnterdate(rs.getString("enterdate"));
				s.setFromcity(rs.getString("fromcity"));
				s.setBirthday(rs.getString("birthday"));
				s.setAddress(rs.getString("address"));
				s.setPass(rs.getString("pass"));
				s.setCreatetime(rs.getString("createtime"));
				list.add(s);
			}
		} catch (SQLException e) {
			// TODO 閼奉亜濮╅悽鐔稿灇閻拷 catch 閸э拷
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, st, con);
		}
		return list;

	}

	public int total() {
		Connection con = DBUtil.getConnection();
		PreparedStatement st = null;
		ResultSet rs = null;
		String sql = "select count(*)  from s_student";
		int total = 0;
		try {
			st = con.prepareStatement(sql);
			rs = st.executeQuery();

			if (rs.next()) {
				total = rs.getInt(1);
			}

		} catch (SQLException e) {
			// TODO 閼奉亜濮╅悽鐔稿灇閻拷 catch 閸э拷
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, st, con);
		}
		return total;
	}

	// 閺屻儴顕楅敍姘壌閹诡喖顫橀崥锟� 閸滃本澧滈張鍝勫娇閿涘奔浜掗崣濠傛勾閸э拷閸嬫碍膩缁﹥鐓＄拠锟�
	// 閺堝褰查懗鑺ユ蒋娴犺埖鐥呴張澶嬪絹娓氭冻绱濇俊鍌涚亯娑撳秵褰佹笟娑崇礉韫囩晫鏆愭潻娆庨嚋閺夆�叉
	public List<Student> paging(String name, String phone, String addr, Long gradeid, String qq) {
		String sql = "select * from s_student where 1=1";
		// 閺�鎯у棘閺侊拷
		List<Object> params = new ArrayList<>();
		// 娑撳秴褰茬悰锟�
//		if(name!=null &&phone!=null && addr!=null) {
//			sql+=" where sanme like ";
//		}
		// int index = 0;

		// 閺勵垯閲滈幀婵婄熅閿涘矁鍤滃杈箖濠婎槞ql閸忔娊鏁�涳拷
		// if (name != null && !"".equals(name)) {
		// sql += " where sname like '%" + name + "%' ";
		//
		// }

		if (name != null && !"".equals(name)) {
			sql += " and sname like   ? ";
			params.add("%" + name + "%");

		}
		if (phone != null && !"".equals(phone)) {
			sql += " and telephone like   ? ";
			params.add("%" + phone + "%");
		}
		if (addr != null && !"".equals(addr)) {
			sql += " and address like   ? ";
			params.add("%" + addr + "%");
		}
		if (gradeid != null && gradeid > 0) {
			sql += " and gradeid like ? ";
			params.add(gradeid);
		}
		if (qq != null && !"".equals(qq)) {
			sql += " and qq like ? ";
			params.add("%" + qq + "%");
		}
		System.out.println(sql + ">>." + params);
		List<Student> list = new ArrayList<>();
		Connection con = DBUtil.getConnection();
		PreparedStatement st = null;
		ResultSet rs = null;
		try {
			st = con.prepareStatement(sql);
			for (int i = 0; i < params.size(); i++) {
				st.setObject(i + 1, params.get(i));
			}
//			st.setString(1, "%" + name + "%");
//			st.setString(2, "%" + phone + "%");
//			st.setString(3, "%" + addr + "%");
			rs = st.executeQuery();
			while (rs.next()) {
				Student s = new Student();
				s.setStuno(rs.getInt("stuno"));
				s.setSname(rs.getString("sname"));
				s.setSex(rs.getString("sex"));
				s.setTelephone(rs.getString("telephone"));
				s.setQq(rs.getString("qq"));
				s.setWechat(rs.getString("wechat"));
				s.setGradeid(rs.getInt("gradeid"));
				s.setEnterdate(rs.getString("enterdate"));
				s.setFromcity(rs.getString("fromcity"));
				s.setBirthday(rs.getString("birthday"));
				s.setAddress(rs.getString("address"));
				s.setPass(rs.getString("pass"));

				list.add(s);
			}
		} catch (SQLException e) {
			// TODO 閼奉亜濮╅悽鐔稿灇閻拷 catch 閸э拷
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, st, con);
		}
		return list;
	}

	public int total(String name, String phone, String addr, Long gradeid, Date enterDateStart, String qq) {
		String sql = "select count(*) from s_student where 1=1 ";
		List<Object> params = new ArrayList<>();
		int r = 0;
		if (name != null && !"".equals(name)) {
			sql += " and sname like ? ";
			params.add("%" + name + "%");
		}
		if (phone != null && !"".equals(phone)) {
			sql += " and telephone like ? ";
			params.add("%" + phone + "%");
		}
		if (addr != null && !"".equals(addr)) {
			sql += " and address like ? ";
			params.add("%" + addr + "%");
		}
		if (gradeid != null) {
			sql += " and gradeid = ? ";
			params.add(gradeid);
		}
		if (enterDateStart != null) {
			sql += " and enterdate = ? ";
			params.add(enterDateStart == null ? null : new java.sql.Date(enterDateStart.getTime()));
		}
		if (qq != null && !"".equals(qq)) {
			sql += " and qq like ? ";
			params.add("%" + qq + "%");
		}
		System.out.println(sql);
		Connection con = DBUtil.getConnection();
		PreparedStatement pre = null;
		ResultSet rs = null;
		try {
			pre = con.prepareStatement(sql);
			for (int i = 0; i < params.size(); i++) {
				pre.setObject(i + 1, params.get(i));
			}
			rs = pre.executeQuery();
			if (rs.next()) {
				r = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		} finally {
			DBUtil.close(rs, pre, con);
		}
		return r;
	}

}